package cn.com.cifi.mars.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.time.LocalDateTime;
import java.util.List;
import java.util.stream.Collectors;

import cn.com.cifi.mars.bean.bo.StartAndEndTimeBO;
import cn.com.cifi.mars.bean.vo.signPlan.signOverall.MonthSignVo;
import cn.com.cifi.mars.bean.vo.signPlan.signOverall.OverallSignPlanVo;
import cn.com.cifi.mars.bean.vo.signPlan.signOverall.YearSignVo;
import cn.com.cifi.mars.constant.Constants;
import cn.com.cifi.mars.constant.WhetherEnum;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import lombok.extern.slf4j.Slf4j;

/**
 *
 * 类名称：OverallSignPlanTemplate
 * 类描述：全盘签约计划模板
 * 创建人：jyx
 * 创建时间：2020年01月07日 上午11:20:00
 * @version 1.0.0
 */

@Slf4j
public class OverallSignPlanTemplate {
    private static final String TEMPLATE_PATH = "/template/overall_sign_template.xlsx";

    private XSSFWorkbook workbook;

    private XSSFSheet sheet;

    //默认sheet索引
    private int defaultSheetIndex = 0;
    //数据开始行
    private int dataBeginRow = 4;
    //当前行标
    private int currentDataRowCursor = dataBeginRow;
    //月份动态列模板开始索引
    private int dynamicColumnBeginIndex = 31;
    //当前列标
    private int currentDataColumnCursor = dynamicColumnBeginIndex;
    //单元格样式
    private XSSFCellStyle cellStyle;
    //表头单元格样式
    private XSSFCellStyle cellStyleZ;
    //汇总行文字单元格样式
    private XSSFCellStyle cellStyleA1;
    //汇总行权益比例单元格样式
    private XSSFCellStyle cellStyleA2;
    //汇总行数值单元格样式(数字-两位小数)
    private XSSFCellStyle cellStyleA3;
    //汇总行数值单元格样式（数字-0位小数）
    private XSSFCellStyle cellStyleA4;
    //汇总行字符串数值单元格样式
    private XSSFCellStyle cellStyleA5;
    //汇总行百分比单元格样式
    private XSSFCellStyle cellStyleA6;

    //数据行文字单元格样式
    private XSSFCellStyle cellStyleB1;
    //数据行权益比例单元格样式
    private XSSFCellStyle cellStyleB2;
    //数据行数值单元格样式(数字-两位小数)
    private XSSFCellStyle cellStyleB3;
    //数据行数值单元格样式（数字-0位小数）
    private XSSFCellStyle cellStyleB4;
    //数据行百分比单元格样式
    private XSSFCellStyle cellStyleB5;

    //权益比例
    private static final String EQUITY_RATIO  = "equityRatio";
    // 数字-两位小数
    private static final String NUMERIC_TWO  = "numericByTwo";
    //数字-0位小数
    private static final String NUMERIC_ZERO  = "numericByZero";
    //达成率
    private static final String ACHIEVING_RATE   = "achievingRate ";

    //套数、面积、均价单元格宽度
    private int columnWidthA;
    //百分比单元格宽度
    private int columnWidthB;
    //金额单元格宽度
    private int columnWidthC;

    public OverallSignPlanTemplate(StartAndEndTimeBO startAndEndTimeBO,String name) {
        //开始时间
        LocalDateTime startDate = startAndEndTimeBO.getStartDate();
        //结束时间
        LocalDateTime endDate = startAndEndTimeBO.getEndDate();
        try {
            InputStream tempInputStream = OverallSignPlanTemplate.class.getResourceAsStream(TEMPLATE_PATH);
            if (tempInputStream != null) {
                workbook = new XSSFWorkbook(tempInputStream);
                sheet = workbook.getSheetAt(defaultSheetIndex);
                setSheetDate(name);

                XSSFRow row = sheet.getRow(defaultSheetIndex);

                cellStyleZ = row.getCell(0).getCellStyle();
                cellStyleA1 = sheet.getRow(4).getCell(0).getCellStyle();
                cellStyleA2 = sheet.getRow(4).getCell(4).getCellStyle();
                cellStyleA3 = sheet.getRow(4).getCell(16).getCellStyle();
                cellStyleA4 = sheet.getRow(4).getCell(17).getCellStyle();
                cellStyleA5 = sheet.getRow(4).getCell(19).getCellStyle();
                cellStyleA6 = sheet.getRow(4).getCell(24).getCellStyle();
                cellStyleB1 = sheet.getRow(5).getCell(0).getCellStyle();
                cellStyleB2 = sheet.getRow(5).getCell(4).getCellStyle();
                cellStyleB3 = sheet.getRow(5).getCell(16).getCellStyle();
                cellStyleB4 = sheet.getRow(5).getCell(19).getCellStyle();
                cellStyleB5 = sheet.getRow(5).getCell(24).getCellStyle();

                columnWidthA = sheet.getColumnWidth(0);
                columnWidthB = sheet.getColumnWidth(4);
                columnWidthC = sheet.getColumnWidth(19);


                for (LocalDateTime time = startDate; LocalDateTimeUtils.compareDate(time, endDate,
                        1); time = LocalDateTimeUtils.addDate(time)) {
                    if(currentDataColumnCursor == 31){
                        XSSFCell cell = row.getCell(currentDataColumnCursor);
                        cell.setCellValue(time.getYear() + "年" + time.getMonthValue() + "月");
                        cell.setCellStyle(cellStyleZ);
                    }else if (currentDataColumnCursor != 31){
                        //创建单元格
                        for (int i = 0; i < 50; i++) {
                            row.createCell(currentDataColumnCursor+i);
                        }
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(0, 0, currentDataColumnCursor, currentDataColumnCursor+49));
                        XSSFCell cell = row.getCell(currentDataColumnCursor);
                        cell.setCellValue(time.getYear() + "年" + time.getMonthValue() + "月");
                        cell.setCellStyle(cellStyleZ);
                        //标题行-动态扩展
                        cellDynamicPropagation(currentDataColumnCursor,"month");
                    }

                    if(time.getMonthValue()==12 || LocalDateTimeUtils.compareDate(time, endDate,2)){
                        //创建单元格
                        for (int i = 50; i < 92; i++) {
                            row.createCell(currentDataColumnCursor+i);
                        }
                        sheet.addMergedRegion(new CellRangeAddress(0, 0, currentDataColumnCursor+50,currentDataColumnCursor+91));
                        row.getCell(currentDataColumnCursor+50).setCellValue(time.getYear()+"年年度合计");
                        row.getCell(currentDataColumnCursor+50).setCellStyle(cellStyleZ);
                        cellDynamicPropagation(currentDataColumnCursor+50,"year");
                        currentDataColumnCursor+=42;
                    }
                    currentDataColumnCursor+=50;
                }
            }
        } catch (Exception e) {
            System.out.println(currentDataColumnCursor);
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 单元格动态扩展
     * @param index 起始坐标
     * @param type 扩展类型(year:年，month:月)
     *
     */
    public void cellDynamicPropagation(int index,String type){
        XSSFRow rowOne = sheet.getRow(1);
        XSSFRow rowTwo = sheet.getRow(2);
        XSSFRow rowThree = sheet.getRow(3);

        if(type.equals("month")){
            for (int i = 0; i < 50; i++) {
                XSSFCell cellOne = rowOne.createCell(index + i);
                XSSFCell cellTwo = rowTwo.createCell(index + i);
                XSSFCell cellThree = rowThree.createCell(index + i);
                cellOne.setCellStyle(cellStyleZ);
                cellTwo.setCellStyle(cellStyleZ);
                cellThree.setCellStyle(cellStyleZ);
            }
            //标题行1,单元格处理
            sheet.addMergedRegion(new CellRangeAddress(1, 1, index, index+14));
            rowOne.getCell(index).setCellValue("月初存货");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, index+15, index+34));
            rowOne.getCell(index+15).setCellValue("本月新供");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, index+35, index+49));
            rowOne.getCell(index+35).setCellValue("月度综合");
            //标题行2,单元格处理
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index, index+3));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+4, index+7));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+8, index+11));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+12, index+12));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+13, index+13));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+14, index+14));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+15, index+18));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+19, index+22));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+23, index+23));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+24, index+27));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+28, index+31));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+32, index+32));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+33, index+33));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+34, index+34));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+35, index+38));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+39, index+42));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+43, index+46));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+47, index+47));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+48, index+48));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+49, index+49));
            rowTwo.getCell(index).setCellValue("月初存货");
            rowTwo.getCell(index+4).setCellValue("签约计划");
            rowTwo.getCell(index+8).setCellValue("签约实际");
            rowTwo.getCell(index+12).setCellValue("签约达成率");
            rowTwo.getCell(index+13).setCellValue("实际去化率");
            rowTwo.getCell(index+14).setCellValue("计划去化率");
            rowTwo.getCell(index+15).setCellValue("供货计划");
            rowTwo.getCell(index+19).setCellValue("供货实际");
            rowTwo.getCell(index+23).setCellValue("供货达成率");
            rowTwo.getCell(index+24).setCellValue("签约计划");
            rowTwo.getCell(index+28).setCellValue("签约实际");
            rowTwo.getCell(index+32).setCellValue("签约达成率");
            rowTwo.getCell(index+33).setCellValue("实际去化率");
            rowTwo.getCell(index+34).setCellValue("计划去化率");
            rowTwo.getCell(index+35).setCellValue("月度可售");
            rowTwo.getCell(index+39).setCellValue("签约计划");
            rowTwo.getCell(index+43).setCellValue("签约实际");
            rowTwo.getCell(index+47).setCellValue("签约达成率");
            rowTwo.getCell(index+48).setCellValue("实际去化率");
            rowTwo.getCell(index+49).setCellValue("计划去化率");

            //标题行3,单元格处理
            rowThree.getCell(index).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+1).setCellValue("面积\n(平米)");
            rowThree.getCell(index+2).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+3).setCellValue("金额\n(万元)");
            rowThree.getCell(index+4).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+5).setCellValue("面积\n(平米)");
            rowThree.getCell(index+6).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+7).setCellValue("金额\n(万元)");
            rowThree.getCell(index+8).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+9).setCellValue("面积\n(平米)");
            rowThree.getCell(index+10).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+11).setCellValue("金额\n(万元)");
            rowThree.getCell(index+15).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+16).setCellValue("面积\n(平米)");
            rowThree.getCell(index+17).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+18).setCellValue("金额\n(万元)");
            rowThree.getCell(index+19).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+20).setCellValue("面积\n(平米)");
            rowThree.getCell(index+21).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+22).setCellValue("金额\n(万元)");
            rowThree.getCell(index+24).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+25).setCellValue("面积\n(平米)");
            rowThree.getCell(index+26).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+27).setCellValue("金额\n(万元)");
            rowThree.getCell(index+28).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+29).setCellValue("面积\n(平米)");
            rowThree.getCell(index+30).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+31).setCellValue("金额\n(万元)");
            rowThree.getCell(index+35).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+36).setCellValue("面积\n(平米)");
            rowThree.getCell(index+37).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+38).setCellValue("金额\n(万元)");
            rowThree.getCell(index+39).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+40).setCellValue("面积\n(平米)");
            rowThree.getCell(index+41).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+42).setCellValue("金额\n(万元)");
            rowThree.getCell(index+43).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+44).setCellValue("面积\n(平米)");
            rowThree.getCell(index+45).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+46).setCellValue("金额\n(万元)");


            sheet.setColumnWidth(index, columnWidthA);
            sheet.setColumnWidth(index+1, columnWidthA);
            sheet.setColumnWidth(index+2, columnWidthA);
            sheet.setColumnWidth(index+3, columnWidthC);
            sheet.setColumnWidth(index+4, columnWidthA);
            sheet.setColumnWidth(index+5, columnWidthA);
            sheet.setColumnWidth(index+6, columnWidthA);
            sheet.setColumnWidth(index+7, columnWidthC);
            sheet.setColumnWidth(index+8, columnWidthA);
            sheet.setColumnWidth(index+9, columnWidthA);
            sheet.setColumnWidth(index+10, columnWidthA);
            sheet.setColumnWidth(index+11, columnWidthC);
            sheet.setColumnWidth(index+12, columnWidthB);
            sheet.setColumnWidth(index+13, columnWidthB);
            sheet.setColumnWidth(index+14, columnWidthB);
            sheet.setColumnWidth(index+15, columnWidthA);
            sheet.setColumnWidth(index+16, columnWidthA);
            sheet.setColumnWidth(index+17, columnWidthA);
            sheet.setColumnWidth(index+18, columnWidthC);
            sheet.setColumnWidth(index+19, columnWidthA);
            sheet.setColumnWidth(index+20, columnWidthA);
            sheet.setColumnWidth(index+21, columnWidthA);
            sheet.setColumnWidth(index+22, columnWidthC);
            sheet.setColumnWidth(index+23, columnWidthB);
            sheet.setColumnWidth(index+24, columnWidthA);
            sheet.setColumnWidth(index+25, columnWidthA);
            sheet.setColumnWidth(index+26, columnWidthA);
            sheet.setColumnWidth(index+27, columnWidthC);
            sheet.setColumnWidth(index+28, columnWidthA);
            sheet.setColumnWidth(index+29, columnWidthA);
            sheet.setColumnWidth(index+30, columnWidthA);
            sheet.setColumnWidth(index+31, columnWidthC);
            sheet.setColumnWidth(index+32, columnWidthB);
            sheet.setColumnWidth(index+33, columnWidthB);
            sheet.setColumnWidth(index+34, columnWidthB);
            sheet.setColumnWidth(index+35, columnWidthA);
            sheet.setColumnWidth(index+36, columnWidthA);
            sheet.setColumnWidth(index+37, columnWidthA);
            sheet.setColumnWidth(index+38, columnWidthC);
            sheet.setColumnWidth(index+39, columnWidthA);
            sheet.setColumnWidth(index+40, columnWidthA);
            sheet.setColumnWidth(index+41, columnWidthA);
            sheet.setColumnWidth(index+42, columnWidthC);
            sheet.setColumnWidth(index+43, columnWidthA);
            sheet.setColumnWidth(index+44, columnWidthA);
            sheet.setColumnWidth(index+45, columnWidthA);
            sheet.setColumnWidth(index+46, columnWidthC);
            sheet.setColumnWidth(index+47, columnWidthB);
            sheet.setColumnWidth(index+48, columnWidthB);
            sheet.setColumnWidth(index+49, columnWidthB);

        }else if(type.equals("year")) {
            for (int i = 0; i < 42; i++) {
                XSSFCell cellOne = rowOne.createCell(index + i);
                XSSFCell cellTwo = rowTwo.createCell(index + i);
                XSSFCell cellThree = rowThree.createCell(index + i);
                cellOne.setCellStyle(cellStyleZ);
                cellTwo.setCellStyle(cellStyleZ);
                cellThree.setCellStyle(cellStyleZ);
            }

            //标题行1,单元格处理
            sheet.addMergedRegion(new CellRangeAddress(1, 1, index, index+8));
            rowOne.getCell(index).setCellValue("年初存货");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, index+9, index+22));
            rowOne.getCell(index+9).setCellValue("年度新供");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, index+23, index+41));
            rowOne.getCell(index+23).setCellValue("年度综合");
            //标题行2,单元格处理
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index, index+3));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+4, index+7));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+8, index+8));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+9, index+12));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+13, index+16));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+17, index+17));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+18, index+21));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+22, index+22));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+23, index+26));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+27, index+30));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+31, index+34));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, index+35, index+38));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+39, index+39));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+40, index+40));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, index+41, index+41));
            rowTwo.getCell(index).setCellValue("年初存货");
            rowTwo.getCell(index+4).setCellValue("实际签约");
            rowTwo.getCell(index+8).setCellValue("实际去化率");
            rowTwo.getCell(index+9).setCellValue("供货计划");
            rowTwo.getCell(index+13).setCellValue("供货实际");
            rowTwo.getCell(index+17).setCellValue("供货达成率");
            rowTwo.getCell(index+18).setCellValue("实际签约");
            rowTwo.getCell(index+22).setCellValue("实际去化率");
            rowTwo.getCell(index+23).setCellValue("年度可售");
            rowTwo.getCell(index+27).setCellValue("年度签约目标");
            rowTwo.getCell(index+31).setCellValue("签约计划");
            rowTwo.getCell(index+35).setCellValue("签约实际");
            rowTwo.getCell(index+39).setCellValue("签约达成率");
            rowTwo.getCell(index+40).setCellValue("实际去化率");
            rowTwo.getCell(index+41).setCellValue("计划去化率");

            //标题行3,单元格处理
            rowThree.getCell(index).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+1).setCellValue("面积\n(平米)");
            rowThree.getCell(index+2).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+3).setCellValue("金额\n(万元)");
            rowThree.getCell(index+4).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+5).setCellValue("面积\n(平米)");
            rowThree.getCell(index+6).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+7).setCellValue("金额\n(万元)");
            rowThree.getCell(index+9).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+10).setCellValue("面积\n(平米)");
            rowThree.getCell(index+11).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+12).setCellValue("金额\n(万元)");
            rowThree.getCell(index+13).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+14).setCellValue("面积\n(平米)");
            rowThree.getCell(index+15).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+16).setCellValue("金额\n(万元)");
            rowThree.getCell(index+18).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+19).setCellValue("面积\n(平米)");
            rowThree.getCell(index+20).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+21).setCellValue("金额\n(万元)");
            rowThree.getCell(index+23).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+24).setCellValue("面积\n(平米)");
            rowThree.getCell(index+25).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+26).setCellValue("金额\n(万元)");
            rowThree.getCell(index+27).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+28).setCellValue("面积\n(平米)");
            rowThree.getCell(index+29).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+30).setCellValue("金额\n(万元)");
            rowThree.getCell(index+31).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+32).setCellValue("面积\n(平米)");
            rowThree.getCell(index+33).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+34).setCellValue("金额\n(万元)");
            rowThree.getCell(index+35).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+36).setCellValue("面积\n(平米)");
            rowThree.getCell(index+37).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+38).setCellValue("金额\n(万元)");

            sheet.setColumnWidth(index, columnWidthA);
            sheet.setColumnWidth(index+1, columnWidthA);
            sheet.setColumnWidth(index+2, columnWidthA);
            sheet.setColumnWidth(index+3, columnWidthC);
            sheet.setColumnWidth(index+4, columnWidthA);
            sheet.setColumnWidth(index+5, columnWidthA);
            sheet.setColumnWidth(index+6, columnWidthA);
            sheet.setColumnWidth(index+7, columnWidthC);
            sheet.setColumnWidth(index+8, columnWidthB);
            sheet.setColumnWidth(index+9, columnWidthA);
            sheet.setColumnWidth(index+10, columnWidthA);
            sheet.setColumnWidth(index+11, columnWidthA);
            sheet.setColumnWidth(index+12, columnWidthC);
            sheet.setColumnWidth(index+13, columnWidthA);
            sheet.setColumnWidth(index+14, columnWidthA);
            sheet.setColumnWidth(index+15, columnWidthA);
            sheet.setColumnWidth(index+16, columnWidthC);
            sheet.setColumnWidth(index+17, columnWidthB);
            sheet.setColumnWidth(index+18, columnWidthA);
            sheet.setColumnWidth(index+19, columnWidthA);
            sheet.setColumnWidth(index+20, columnWidthA);
            sheet.setColumnWidth(index+21, columnWidthC);
            sheet.setColumnWidth(index+22, columnWidthB);
            sheet.setColumnWidth(index+23, columnWidthA);
            sheet.setColumnWidth(index+24, columnWidthA);
            sheet.setColumnWidth(index+25, columnWidthA);
            sheet.setColumnWidth(index+26, columnWidthC);
            sheet.setColumnWidth(index+27, columnWidthA);
            sheet.setColumnWidth(index+28, columnWidthA);
            sheet.setColumnWidth(index+29, columnWidthA);
            sheet.setColumnWidth(index+30, columnWidthC);
            sheet.setColumnWidth(index+31, columnWidthA);
            sheet.setColumnWidth(index+32, columnWidthA);
            sheet.setColumnWidth(index+33, columnWidthA);
            sheet.setColumnWidth(index+34, columnWidthC);
            sheet.setColumnWidth(index+35, columnWidthA);
            sheet.setColumnWidth(index+36, columnWidthA);
            sheet.setColumnWidth(index+37, columnWidthA);
            sheet.setColumnWidth(index+38, columnWidthC);
            sheet.setColumnWidth(index+39, columnWidthB);
            sheet.setColumnWidth(index+40, columnWidthB);
            sheet.setColumnWidth(index+41, columnWidthB);
        }
    }


    public void setSheetDate(String name) {
        workbook.setSheetName(defaultSheetIndex, workbook.getSheetName(defaultSheetIndex).replace("签约计划", name));
    }

    /**
     * 批量插入行
     *
     * @param list 报表数据
     * @param type 报表类型（0:全盘导出 ; 1:版本导出）
     */
    public synchronized void appendRows(List<OverallSignPlanVo> list,int type) {
        if(type == WhetherEnum.YES.getKey()){
            //删除项目行的数据
            list.remove(0);
//            sheet.shiftRows(dataBeginRow+1, dataBeginRow+list.size(), -1);
        }
        for (OverallSignPlanVo vo : list) {
            this.appendRow(vo);
        }

    }

    /**
     * 插入行
     *
     * @param
     */
    public synchronized void appendRow(OverallSignPlanVo vo) {
        XSSFRow row = sheet.getRow(currentDataRowCursor);
        if (row == null) {
            row = sheet.createRow(currentDataRowCursor);
        }
        row.setHeightInPoints(Constants.HEIGHT_POINTS);
        boolean isParentLevel;
        if(null == vo.getProductName()){
            isParentLevel = true;
        }else {
            isParentLevel = false;
        }

        int index = 0;
        //项目信息
        this.appendCell(row, index++,vo.getProjectName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProjectCode(),isParentLevel,"");
        //分期信息
        this.appendCell(row, index++,vo.getStageName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getStageCode(),isParentLevel,"");
        this.appendCell(row, index++,null == vo.getEquityRatio() ? null : vo.getEquityRatio(),isParentLevel,EQUITY_RATIO);
        this.appendCell(row, index++,vo.getOpmarketing(),isParentLevel,"");
        this.appendCell(row, index++,null == vo.getIsAndTable() ? "" :  vo.getIsAndTable() == 1?"是":"否",isParentLevel,"");
        this.appendCell(row, index++,null == vo.getExistingHouses() ? "" : vo.getExistingHouses() == 1?"现房销售":"非现房销售",isParentLevel,"");
        //组团信息
        this.appendCell(row, index++,vo.getGroupName(),isParentLevel,"");
        //产品信息
        this.appendCell(row, index++,vo.getProProductName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProProductCode(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProductName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProductCode(),isParentLevel,"");
        this.appendCell(row, index++,vo.getBusinessTypeName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getFreeTypeName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProTypeName(),isParentLevel,"");

        //全盘供货与签约
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getAvailableNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getAvailableArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getAvailablePrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSupplyActualNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSupplyActualArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSupplyActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSupplyActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSupplyFillRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSignActualNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSignActualArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSignAvgActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSignActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getSignFillRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,vo.getSupplyAndSignVo().getActualRemovalRate(),isParentLevel,ACHIEVING_RATE);

        //年度合计
        List<YearSignVo> yearSignVoList = vo.getYearSignVo();
        //月度全盘签约
        List<MonthSignVo> MonthVoList = vo.getMonthSignVo();
        if(CollectionUtils.isNotEmpty(MonthVoList)) {
            for(int i = 0 ; i < MonthVoList.size() ; i++) {
                //月度合计
                MonthSignVo monthSignVo = MonthVoList.get(i);
                this.appendCell(row, index++,monthSignVo.getEarlyNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlyArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getAvgEarlyPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlyPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlySignPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlyAvgSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlySignActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlyAvgSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getEarlyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getEarlyPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplySignPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplySignPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplySignActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailableNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailableArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailablePrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvgSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvgSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSyntheticalActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSyntheticalPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);


                String[] split = monthSignVo.getYearMonth().split("-");
                String year = split[0];
                String month = split[1];
                if(month.equals("12") || i == MonthVoList.size()-1){
                    YearSignVo yearSignVo = yearSignVoList.stream().filter(y -> y.getYear().equals(year)).collect(Collectors.toList()).get(0);
                    this.appendCell(row, index++,yearSignVo.getEarlyNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getEarlyArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getEarlyAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getEarlyPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getEarlySignActualNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getEarlySignActualArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getEarlySignActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getEarlySignActualPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getEarlyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                    this.appendCell(row, index++,yearSignVo.getSupplyPlanNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSupplyPlanArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSupplyPlanAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSupplyPlanPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSupplyActualNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSupplyActualArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSupplyActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSupplyActualPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSupplyFillRate(),isParentLevel,ACHIEVING_RATE);
                    this.appendCell(row, index++,yearSignVo.getSupplySignActualNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSupplySignActualArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSupplySignActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSupplySignActualPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSupplyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalAvailableNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalAvailableArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalAvailablePrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getYearSigningTargetCount(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getYearSigningTargetArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getYearSigningTargetAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getYearSigningTargetAmount(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualNum(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualArea(),isParentLevel,NUMERIC_TWO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalSignFillRate(),isParentLevel,ACHIEVING_RATE);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                    this.appendCell(row, index++,yearSignVo.getSyntheticalPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
                }
            }
        }
        currentDataRowCursor++;
    }

    /**
     * 插入列
     * @param row 行
     * @param index 列数
     * @param value 值
     * @param isParentLevel 是否汇总行
     * @param type 样式类型
     */
    private void appendCell(XSSFRow row, int index, String value, boolean isParentLevel, String type) {
        XSSFCell cell = row.getCell(index);
        if (cell == null) {
            cell = row.createCell(index);
        }

        if(isParentLevel){
            switch (type){
                case EQUITY_RATIO:
                    cell.setCellStyle(cellStyleA2);
                    if(value == null){
                        cell.setCellValue("");
                    }else {
                        cell.setCellValue(Double.valueOf(value)/100);
                    }
                    break;
                case NUMERIC_TWO:
                    if(value.contains("/")){
                        cell.setCellStyle(cellStyleA5);
                        cell.setCellValue(value);
                    }else {
                        cell.setCellStyle(cellStyleA3);
                        cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace(",","")));
                    }
                    break;
                case NUMERIC_ZERO:
                    if(value.contains("/")){
                        cell.setCellStyle(cellStyleA5);
                        cell.setCellValue(value);
                    }else {
                        cell.setCellStyle(cellStyleA4);
                        cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace(",","")));
                    }
                    break;
                case ACHIEVING_RATE:
                    cell.setCellStyle(cellStyleA6);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace("%","").replace(",",""))/100);
                    break;
                default:
                    cell.setCellStyle(cellStyleA1);
                    cell.setCellValue(StrUtils.isEmpty(value) ? "" : value);
                    break;
            }

        }else {
            switch (type){
                case EQUITY_RATIO:
                    cell.setCellStyle(cellStyleB2);
                    if(value == null){
                        cell.setCellValue("");
                    }else {
                        cell.setCellValue(Double.valueOf(value)/100);
                    }
                    break;
                case NUMERIC_TWO:
                    cell.setCellStyle(cellStyleB3);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace(",","")));
                    break;
                case NUMERIC_ZERO:
                    cell.setCellStyle(cellStyleB4);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace(",","")));
                    break;
                case ACHIEVING_RATE:
                    cell.setCellStyle(cellStyleB5);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace("%","").replace(",",""))/100);
                    break;
                default:
                    cell.setCellStyle(cellStyleB1);
                    cell.setCellValue(StrUtils.isEmpty(value) ? "" : value);
                    break;
            }
        }
    }

    /**
     * 导出
     *
     * @param outputStream
     * @throws IOException
     */
    public void write(OutputStream outputStream) throws IOException {
        workbook.write(outputStream);
    }
}
